In databases, ACID stands for Atomicity, Consistency (Committed), Isolation, and Durability. These features are considered to be among the key properties required of a database management system, or DBMS, because without them, the integrity of the database cannot be guaranteed. In practice, these properties are often relaxed somewhat to provide better performance. Within the context of database management, a single logical operation on the data of the database is called a transaction. For example, transferring funds from one account to another, even though it consists of multiple individual operations (such as debiting one account and crediting a second account) is a single transaction because if just the debiting is performed, or just the crediting is performed, the database data will be inconsistent.
Atomicity refers to the ability of the DBMS to guarantee that either all of the tasks of a transaction are performed or that none of the tasks are performed. To continue the example above, the transfer of funds can be completed or it can fail, but atomicity guarantees that the first account will not be debited if the second account is not credited and vice versa.
Consistency ensures that the database is in a legal state when a transaction begins and ends. A transaction is not allowed to violate the integrity constraints of the database. For example, if an integrity constraint or rule states that all accounts must have a positive balance, then any transaction that takes the balance to a negative number violates this rule and is aborted. When a transaction is aborted, it is rolled back, that is, a rollback operation undoes all the work performed in the transaction and the database is returned to the consistent state it was in before rollback was performed. A “commit” operation is the opposite of a “rollback”. A commit operation generally makes a set of tentative changes permanent. In SQL for example, a transaction begins with a BEGIN statement, includes one or more SQL statements and ends with a COMMIT statement. The COMMIT statement makes the changes made by the transaction visible to other users and releases or updates any checkpoints that were saved. In contrast, the ROLLBACK statement undoes all the work performed since the BEGIN statement was issued.
Isolation refers to the ability of an application to make operations in a transaction appear isolated from all other operations. The isolation property is the most often relaxed ACID property in a DBMS because to maintain the highest level of isolation a DBMS must acquire locks on data, which may result in a loss of concurrency or cause performance problems.
Durability refers to the guarantee that once a user has been notified of success, the transaction will persist, and will not be undone: it will survive system failure, and the database system has checked the integrity constraints and will not abort and roll back the transaction. Typically, all transactions are written into a log that can be played back to recreate the system to a state some time before the failure. A transaction is usually considered “committed” after it has been written to the log, thus when a database is recovered, it is typically recovered back to the last (most recent) committed transaction. This ACID property is occasionally relaxed on databases with “lazy” commit, whereby the committed data may not be immediately written to the transaction log.
Logging in the database context refers to the practice of saving a copy of transactions applied to a database so that in the event that the program or system crashes, the transactions can be reapplied to the database to ensure consistent data. Logging can also be used in the event that the active database is no longer available or has become corrupted, to reapply transactions to a backup copy of the database to return the database to its pre-failure state or to some approximation thereof. Write ahead logging (WAL) refers generally to techniques for providing atomicity and durability in database systems. In a system that uses WAL, all modifications (or compensating undo data) are written to a log before they are applied to the database. WAL allows updates of the database to be done in-place, which is generally considered preferable to the alternative, copy-on-write.
Shadow paging is not in-place updating. A page in the context of shadow paging refers to a unit of physical storage (typically on a hard disk), of the order of 210 to 215 bytes. Shadow paging is a copy-on-write technique that avoids in-place updates of pages. Instead, when a page is to be modified, a shadow page is allocated. Since the shadow page has no references (from other pages on disk), it can be modified without worrying about consistency constraints, etc. When the page is ready to be persisted, all the pages that referred to the original page are updated to refer to the new replacement page instead. Because the replacement page is “activated” only when it is ready, it is atomic. If the pages that referred to the original page are also updated via shadow paging, this procedure may recurse many times, becoming quite costly in terms of performance. Shadow paging is not germane to this discussion.